package zhongqi.jdbc.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import lombok.extern.slf4j.Slf4j;


import com.mysql.jdbc.utils.ConnectionUtils;



/**
 * @author  JYM
 * 2016-10-10 下午7:24:02
 */
@Slf4j
public class ConnectionDao {

	//T10全表查询
	public List<User> selectT10() {		
		 //String url="jdbc:mysql://10.5.150.212:3306/test";
		 String url="jdbc:mysql://127.0.0.1:3306/test";
	     String user="root";
	     String pwd="123456";
	     String sql="select * from t10";
	  
		List<User>  wxScoreInTrades=new ArrayList<User>();
			try
		    {
		       Class.forName("com.mysql.jdbc.Driver");
		        ResultSet rs=ConnectionUtils.getSelectResultSet(url, user, pwd, sql);
		        log.info("处理结果集！");
		  //处理结果集
		        while (rs.next())
			      {
			    	User wxScoreInTrade=new User();
			    	wxScoreInTrade.setId(rs.getInt("id"));
			    	wxScoreInTrade.setAccesstime(rs.getString("accesstime"));
			    	wxScoreInTrade.setClickurl(rs.getString("clickurl"));
			    	wxScoreInTrade.setKeyword(rs.getString("keyword"));
			    	wxScoreInTrade.setRanked_clickno(rs.getString("ranked_clickno"));
			    	wxScoreInTrade.setUserid(rs.getString("userid"));
			       wxScoreInTrades.add(wxScoreInTrade);
			      }
		      log.info("处理完结果集，关闭所有连接对象！");
		      ConnectionUtils.freeRs(rs, url, user, pwd);
		      log.info("返回数据！");
		      return   wxScoreInTrades;
		    }catch (Exception ex)
			    {
		    	    ex.printStackTrace();
			    }
		  return null;
	}
	
	//T20全表查询
	public List<User> selectT20() {		
			 //String url="jdbc:mysql://10.5.150.212:3306/test";
			 String url="jdbc:mysql://127.0.0.1:3306/test";
		     String user="root";
		     String pwd="123456";
		     String sql="select * from t20";
		  
			List<User>  wxScoreInTrades=new ArrayList<User>();
				try
			    {
			       Class.forName("com.mysql.jdbc.Driver");
			        ResultSet rs=ConnectionUtils.getSelectResultSet(url, user, pwd, sql);
			        log.info("处理结果集！");
			  //处理结果集
			        while (rs.next())
				      {
				    	User wxScoreInTrade=new User();
				    	wxScoreInTrade.setId(rs.getInt("id"));
				    	wxScoreInTrade.setAccesstime(rs.getString("accesstime"));
				    	wxScoreInTrade.setClickurl(rs.getString("clickurl"));
				    	wxScoreInTrade.setKeyword(rs.getString("keyword"));
				    	wxScoreInTrade.setRanked_clickno(rs.getString("ranked_clickno"));
				    	wxScoreInTrade.setUserid(rs.getString("userid"));
				       wxScoreInTrades.add(wxScoreInTrade);
				      }
			      log.info("处理完结果集，关闭所有连接对象！");
			      ConnectionUtils.freeRs(rs, url, user, pwd);
			      log.info("返回数据！");
			      return   wxScoreInTrades;
			    }catch (Exception ex)
				    {
			    	    ex.printStackTrace();
				    }
			  return null;
		}
		
		//T30全表查询
		public List<User> selectT30() {		
			 //String url="jdbc:mysql://10.5.150.212:3306/test";
			 String url="jdbc:mysql://127.0.0.1:3306/test";
		     String user="root";
		     String pwd="123456";
		     String sql="select * from t30";
		  
			List<User>  wxScoreInTrades=new ArrayList<User>();
				try
			    {
			       Class.forName("com.mysql.jdbc.Driver");
			        ResultSet rs=ConnectionUtils.getSelectResultSet(url, user, pwd, sql);
			        log.info("处理结果集！");
			  //处理结果集
			        while (rs.next())
				      {
				    	User wxScoreInTrade=new User();
				    	wxScoreInTrade.setId(rs.getInt("id"));
				    	wxScoreInTrade.setAccesstime(rs.getString("accesstime"));
				    	wxScoreInTrade.setClickurl(rs.getString("clickurl"));
				    	wxScoreInTrade.setKeyword(rs.getString("keyword"));
				    	wxScoreInTrade.setRanked_clickno(rs.getString("ranked_clickno"));
				    	wxScoreInTrade.setUserid(rs.getString("userid"));
				       wxScoreInTrades.add(wxScoreInTrade);
				      }
			      log.info("处理完结果集，关闭所有连接对象！");
			      ConnectionUtils.freeRs(rs, url, user, pwd);
			      log.info("返回数据！");
			      return   wxScoreInTrades;
			    }catch (Exception ex)
				    {
			    	    ex.printStackTrace();
				    }
			  return null;
		}
		
		//T40全表查询
		public List<User> selectT40() {		
			 //String url="jdbc:mysql://10.5.150.212:3306/test";
			 String url="jdbc:mysql://127.0.0.1:3306/test";
		     String user="root";
		     String pwd="123456";
		     String sql="select * from t40";
		  
			List<User>  wxScoreInTrades=new ArrayList<User>();
				try
			    {
			       Class.forName("com.mysql.jdbc.Driver");
			        ResultSet rs=ConnectionUtils.getSelectResultSet(url, user, pwd, sql);
			        log.info("处理结果集！");
			  //处理结果集
			        while (rs.next())
				      {
				    	User wxScoreInTrade=new User();
				    	wxScoreInTrade.setId(rs.getInt("id"));
				    	wxScoreInTrade.setAccesstime(rs.getString("accesstime"));
				    	wxScoreInTrade.setClickurl(rs.getString("clickurl"));
				    	wxScoreInTrade.setKeyword(rs.getString("keyword"));
				    	wxScoreInTrade.setRanked_clickno(rs.getString("ranked_clickno"));
				    	wxScoreInTrade.setUserid(rs.getString("userid"));
				       wxScoreInTrades.add(wxScoreInTrade);
				      }
			      log.info("处理完结果集，关闭所有连接对象！");
			      ConnectionUtils.freeRs(rs, url, user, pwd);
			      log.info("返回数据！");
			      return   wxScoreInTrades;
			    }catch (Exception ex)
				    {
			    	    ex.printStackTrace();
				    }
			  return null;
		}
	
	//缓存查询（带where条件）
	public List<User> select_with_where(String user_sql) {
		 //String url="jdbc:mysql://10.5.150.212:3306/test";
		 String url="jdbc:mysql://127.0.0.1:3306/test";
	     String user="root";
	     String pwd="123456";
	     //String sql="select * from t100";
	     //测试缓存查询
	     String sql="select * from t10 where id=1";
	     if(user_sql != null){
	    	 sql = user_sql;
	     }
	  
		List<User>  wxScoreInTrades=new ArrayList<User>();
			try
		    {
		       Class.forName("com.mysql.jdbc.Driver");
		        ResultSet rs=ConnectionUtils.getSelectResultSet(url, user, pwd, sql);
		        log.info("处理结果集！");
		  //处理结果集
		        while (rs.next())
			      {
			    	User wxScoreInTrade=new User();
			    	wxScoreInTrade.setId(rs.getInt("id"));
			    	wxScoreInTrade.setAccesstime(rs.getString("accesstime"));
			    	wxScoreInTrade.setClickurl(rs.getString("clickurl"));
			    	wxScoreInTrade.setKeyword(rs.getString("keyword"));
			    	wxScoreInTrade.setRanked_clickno(rs.getString("ranked_clickno"));
			    	wxScoreInTrade.setUserid(rs.getString("userid"));
			       wxScoreInTrades.add(wxScoreInTrade);
			      }
		      log.info("处理完结果集，关闭JDBC对象！");
		      ConnectionUtils.freeRs(rs, url, user, pwd);
		      log.info("返回数据！");
		      return   wxScoreInTrades;
		    }catch (Exception ex)
			    {
		    	    ex.printStackTrace();
			    }
		  return null;
	}
		
	
	//更新数据
	public void update(String user_sql) {
		String url="jdbc:mysql://127.0.0.1:3306/test";
	    String user="root";
	    String pwd="123456";
	
	    try{
	    	Class.forName("com.mysql.jdbc.Driver");
	    	ConnectionUtils.update(url, user, pwd, user_sql);
	    	
	    }catch (Exception ex){
	    	ex.printStackTrace();
	    	}
	}
	
	//删除数据
	public void delete(String user_sql) {
		String url="jdbc:mysql://127.0.0.1:3306/test";
	    String user="root";
	    String pwd="123456";
	
	    try{
	    	Class.forName("com.mysql.jdbc.Driver");
	    	ConnectionUtils.delete(url, user, pwd, user_sql);
	    	
	    }catch (Exception ex){
	    	ex.printStackTrace();
	    	}
	}
	
	//新增数据
	public void  insert(String user_sql) {
		String url="jdbc:mysql://127.0.0.1:3306/test";
	    String user="root";
	    String pwd="123456";
	
	    try{
	    	Class.forName("com.mysql.jdbc.Driver");
		    ConnectionUtils.insert(url, user, pwd, user_sql);
		  
	    }catch (Exception ex){
	    	ex.printStackTrace();
	    	}
	}
	
}
